package com.css.fxfzdzzh.modules.evaluate.repository.impl;

import com.alibaba.fastjson.JSONObject;
import com.css.fxfzdzzh.constants.FxfzConstants;
import com.css.fxfzdzzh.constants.YNEnum;
import com.css.fxfzdzzh.enums.DisasterTypeEnum;
import com.css.fxfzdzzh.enums.ResultEnum;
import com.css.fxfzdzzh.enums.TaskEnum;
import com.css.fxfzdzzh.modules.evaluate.VO.PolygonEvaluateResultVO;
import com.css.fxfzdzzh.modules.evaluate.VO.SoftSoilQueryVO;
import com.css.fxfzdzzh.modules.evaluate.entity.SoftSoilDrill;
import com.css.fxfzdzzh.modules.evaluate.entity.SoftSoilResult;
import com.css.fxfzdzzh.modules.evaluate.entity.TaskVersion;
import com.css.fxfzdzzh.modules.evaluate.repository.SoftSoilRepository;
import com.css.fxfzdzzh.modules.taskManage.entity.TaskDistrictArea;
import com.css.fxfzdzzh.modules.taskManage.entity.TaskEntity;
import com.css.fxfzdzzh.modules.taskManage.entity.TaskEntityVO;
import com.css.fxfzdzzh.util.CreateGson;
import com.css.fxfzdzzh.util.PlatformObjectUtils;
import com.css.fxfzdzzh.web.PlatformSessionContext;
import com.css.fxfzdzzh.zcpt.sys.entity.SUser;
import com.css.fxfzdzzh.zcpt.sys.service.SUserService;
import com.css.fxfzdzzh.zcpt.sys.service.SysAreaService;
import com.css.fxfzdzzh.zcpt.sys.service.SysRoleService;
import org.apache.regexp.RE;
import org.hibernate.engine.jdbc.batch.spi.Batch;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import javax.xml.transform.Result;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author Dbj
 * @date 2023年02月14日  15:18
 */
@Repository
public class SoftSoilRepositoryImpl implements SoftSoilRepository {

    @Resource
    JdbcTemplate jdbcTemplate;
    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    @Resource
    SUserService sUserService;

    @Resource
    SysRoleService sysRoleService;

    @Resource
    SysAreaService sysAreaService;

    //private String userId = "80cd530287e54c6788925adf2dfc9da1";
    @Override
    public Map<String, Object> findTask(SoftSoilQueryVO vo, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("select t.dt_id,t.task_num,t.task_name,t.task_district,t.task_status,t.create_user,t.create_time," +
                "t.disaster_type,r.dtcv_id from dzzh_task t left join dzzh_task_center_version r on t.dt_id = r.task_id and r.del_flag='0' and r.result_status='2'");
        StringBuilder whereSql = new StringBuilder(" where t.del_flag = '"+ YNEnum.N.toString() +"' " +
                "and t.create_user = '" + PlatformSessionContext.getUserID() + "' ");
        StringBuilder orderSql = new StringBuilder(" order by t.create_time desc");
        StringBuilder countSql = new StringBuilder(" select count(*) from dzzh_task t");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;

        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        if (!PlatformObjectUtils.isEmpty(vo.getTaskNum())) {
            whereSql.append(" and t.task_num like '%" + vo.getTaskNum() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(vo.getTaskName())) {
            whereSql.append(" and t.task_name like '%" + vo.getTaskName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(vo.getTaskStatus())) {
            whereSql.append(" and t.task_status = '" + vo.getTaskStatus() + "'");
        }else{
            whereSql.append(" and (t.task_status = '" + TaskEnum.ING.getState() + "' or t.task_status='"+TaskEnum.END.getState()+"')");
        }
        if (!PlatformObjectUtils.isEmpty(vo.getProvince()) || !PlatformObjectUtils.isEmpty(vo.getCity()) || !PlatformObjectUtils.isEmpty(vo.getArea())) {
            whereSql.append(" and (");

            if (!PlatformObjectUtils.isEmpty(vo.getArea())) {
                String city = "-"+vo.getCity();
                if(vo.getCity().equals(vo.getProvince())){
                    city = "";
                }
                whereSql.append("  (t.task_district like '%" + vo.getProvince()+city+"-"+vo.getArea() + "%') or (t.task_district = '" + vo.getProvince()+city+ "') or (t.task_district like '%" + vo.getProvince()+city+"、%') or  (t.task_district like '%" + vo.getProvince()+ "、%') or  (t.task_district = '" + vo.getProvince()+ "')");
            }else if (!PlatformObjectUtils.isEmpty(vo.getCity())) {
                String city = "-"+vo.getCity();
                if(vo.getCity().equals(vo.getProvince())){
                    city = "";
                }
                whereSql.append("  (t.task_district like '%" + vo.getProvince()+city + "%') or  (t.task_district like '%" + vo.getProvince()+ "、%') or  (t.task_district = '" + vo.getProvince()+ "')");
            }else if (!PlatformObjectUtils.isEmpty(vo.getProvince())) {
                whereSql.append("   (t.task_district like '%" + vo.getProvince()+ "%') ");
            }


            whereSql.append(") ");
        }
        whereSql.append(" and t.disaster_type like '%" + DisasterTypeEnum.SUBSIDENCE.getState() + "%'");
        String sqlWhole = sql.append(whereSql).append(orderSql).append(limitSql).toString();
        countSql.append(whereSql);
        List<TaskEntityVO> list = jdbcTemplate.query(sqlWhole, new BeanPropertyRowMapper<>(TaskEntityVO.class));
        Integer total = jdbcTemplate.queryForObject(countSql.toString(), Integer.class);
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

//    @Override
//    public void deleteTaskDrillVersion(String taskId, String versionCode,String disasterType) {
//        String sql = "delete from dzzh_task_version where task_id = '"+taskId+"' and  version_code='"+versionCode+"' and disaster_type='"+disasterType+"'";
//        jdbcTemplate.update(sql);
//    }

    @Override
    public void saveTaskDrillVersion(TaskVersion taskVersion) {
        String sql = "insert into dzzh_task_version (dtcv_id,task_id,version_code,disaster_type,create_user,create_user_name,create_time) values " +
                "(:dtcvId,:taskId,:versionCode,:disasterType,:createUser,:createUserName,:createTime)";
        BeanPropertySqlParameterSource source = new BeanPropertySqlParameterSource(taskVersion);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void save(SoftSoilResult entity) {
        String sql = "insert into dzzh_soft_soil_evaluate_result " +
                "(sser_id,drill_code,longitude,latitude,geotechnical_designation,shear_wave_velocity," +
                "layer_thickness,intensity7,intensity8,intensity9,is_soft7,is_soft8,is_soft9,dtcv_id,geom) " +
                "values " +
                "(:sserId,:drillCode,:longitude,:latitude,:geotechnicalDesignation,:shearWaveVelocity" +
                ",:layerThickness,:intensity7,:intensity8,:intensity9,:isSoft7,:isSoft8,:isSoft9" +
                ",:dtcvId,ST_GeometryFromText(:geom,4490))";
        BeanPropertySqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void delete(String taskId) {
        String sql = "update dzzh_soft_soil_evaluate_result set del_flag='"+YNEnum.Y.toString()+"' where task_id = '"+taskId+"' and  del_flag='"+YNEnum.N.toString()+"' and result_status='"+ ResultEnum.FINA.getState() +"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public void deleteResultDataById(String dtcvId) {
        String sql = "delete from dzzh_soft_soil_evaluate_result where dtcv_id='"+dtcvId+"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public void delete(String taskId,String versionCode) {
        String sql = "delete from dzzh_soft_soil_drill where task_id='"+taskId+"' and version_code='"+versionCode+"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public TaskVersion findDataByTaskId(String taskId,String resultStatus) {
        String sql = "select * from dzzh_task_center_version where task_id='"+taskId+"' and del_flag='"+YNEnum.N.toString()+"' and result_status='"+resultStatus+"'";
        List<TaskVersion> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(TaskVersion.class));
        return PlatformObjectUtils.isNotEmpty(query)?query.get(0):new TaskVersion();
    }

    @Override
    public Map<String, Object> getCenterDataPage(String taskId, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("select * from dzzh_task_center_version");
        StringBuilder whereSql = new StringBuilder(" where task_id='"+taskId+"' and disaster_type='"+ DisasterTypeEnum.SUBSIDENCE.getState()+ "' and result_status='"+ResultEnum.CENTER.getState()+"' and del_flag='0'");
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        StringBuilder countSql = new StringBuilder(" select count(*) from dzzh_task_center_version");
        int firstIndex = (curPage - 1) * pageSize;
        StringBuilder limitSql = new StringBuilder(" limit " + pageSize + " offset " + firstIndex);
        String sqlWhole = sql.append(whereSql).append(orderSql).append(limitSql).toString();
        countSql.append(whereSql);
        List<TaskVersion> list = jdbcTemplate.query(sqlWhole, new BeanPropertyRowMapper<>(TaskVersion.class));
        Integer total = jdbcTemplate.queryForObject(countSql.toString(), Integer.class);
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

//    @Override
//    public void deleteTaskCenterVersion(String taskId, String versionCode, String disasterType) {
//        String sql = "delete from dzzh_task_center_version where task_id = '"+taskId+"' and  version_code='"+versionCode+"' and disaster_type='"+disasterType+"'";
//        jdbcTemplate.update(sql);
//    }
//
//    @Override
//    public void deleteTaskCenterResult(String taskId, String versionCode, String disasterType) {
//        String sql = "update dzzh_soft_soil_evaluate_result set del_flag= '"+YNEnum.Y.toString()+"' where task_id = '"+taskId+"' and  version_code='"+versionCode+"'";
//        jdbcTemplate.update(sql);
//    }

    @Override
    public void saveTaskCenterVersion(TaskVersion version) {
        String sql = "insert into dzzh_task_center_version (task_id,version_code,disaster_type,create_user,create_user_name,create_time,data_from,evaluate_user,evaluate_user_name,evaluate_time,result_status,del_flag,dtcv_id) values " +
                "(:taskId,:versionCode,:disasterType,:createUser,:createUserName,:createTime,:dataFrom,:evaluateUser,:evaluateUserName,:evaluateTime,:resultStatus,:delFlag,:dtcvId)";
        BeanPropertySqlParameterSource source = new BeanPropertySqlParameterSource(version);
        namedParameterJdbcTemplate.update(sql, source);
    }

//    @Override
//    public List<SoftSoilResult> findDataByTaskIdAndVersionCode(String taskId, String versionCode) {
//        String sql = "select * from dzzh_soft_soil_evaluate_result where task_id='"+taskId+"' and del_flag='"+YNEnum.N.toString()+"' and result_status='2' and version_code='"+versionCode+"'";
//        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(SoftSoilResult.class));
//    }

//    @Override
//    public void updateToCenterResult(SoftSoilResult entity) {
//        String sql="update dzzh_soft_soil_evaluate_result set result_status = :resultStatus, create_center_user = :createCenterUser, create_center_user_name = :createCenterUserName, create_center_time = :createCenterTime " +
//                " where task_id= :TaskId and result_status='"+ResultEnum.FINA.getState()+"' and del_flag='"+YNEnum.N.toString()+"'";
//        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
//        namedParameterJdbcTemplate.update(sql, source);
//    }
//
//    @Override
//    public void abandonSoftSoilResult(String sserId) {
//        String sql="update dzzh_soft_soil_evaluate_result set result_status = '"+ResultEnum.TEMP.getState()+"' where sser_id= :sserId and result_status='"+ResultEnum.FINA.getState()+"' and del_flag='"+YNEnum.N.toString()+"'";
//        jdbcTemplate.update(sql);
//    }

    @Override
    public int importDrillData(List<SoftSoilDrill> list) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(list.toArray());
        String sql =
                "insert into dzzh_soft_soil_drill(dssd_id,"
                        + "task_id,"
                        + "version_code,"
                        + "drill_code,"
                        + "longitude,"
                        + "latitude,"
                        + "geotechnical_designation,"
                        + "layer_thickness,"
                        + "shear_wave_velocity,"
                        + "create_user,"
                        + "create_time) "
                        + " values("
                        + ":dssdId, "
                        + ":taskId, "
                        + ":versionCode, "
                        + ":drillCode, "
                        + ":longitude, "
                        + ":latitude, "
                        + ":geotechnicalDesignation, "
                        + ":layerThickness,"
                        + ":shearWaveVelocity,"
                        + ":createUser,"
                        + ":createTime"
                        + " )";
        int[] ints = namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
        return ints.length;
    }

//    @Override
//    public TaskVersion getTaskVersion(String taskId,String disasterType){
//        String sql = "select * from dzzh_task_center_version where task_id='"+taskId+"' and disaster_type='"+disasterType+"'";
//        List<TaskVersion> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(TaskVersion.class));
//        return PlatformObjectUtils.isNotEmpty(query)? query.get(0):new TaskVersion();
//    }

    @Override
    public List<SoftSoilDrill> getImportDrillData(String taskId, String versionCode) {
        String sql = "select * from dzzh_soft_soil_drill where task_id='"+taskId+"' and version_code='"+versionCode+"'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(SoftSoilDrill.class));
    }

//    @Override
//    public void deleteTaskFinalResult(String taskId, String versionCode) {
//        String sql = "update dzzh_soft_soil_evaluate_result set del_flag= '"+YNEnum.Y.toString()+"' where task_id = '"+taskId+"' and  version_code='"+versionCode+"' and del_flag='"+YNEnum.N.toString()+"'";
//        jdbcTemplate.update(sql);
//    }

    @Override
    public void deleteTaskModelFinalResult(String taskId) {
        String sql = "update dzzh_task_center_version set del_flag= '"+YNEnum.Y.toString()+"',update_user='"+PlatformSessionContext.getUserID()+"'" +
                ",update_time=now() where task_id = '"+taskId+"'  and del_flag='"+YNEnum.N.toString()+"' and result_status='"+ ResultEnum.FINA.getState() +"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public void deleteModelDataById(String dtcvId) {
        String sql = "delete from  dzzh_task_center_version where dtcv_id='"+dtcvId+"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public void deleteTaskModelCenterResult(String dtcvId) {
        String sql = "update dzzh_task_center_version set del_flag= '"+YNEnum.Y.toString()+"',update_user='"+PlatformSessionContext.getUserID()+"'" +
                ",update_time=now() where dtcv_id = '"+dtcvId+"' and  del_flag='"+YNEnum.N.toString()+"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public void saveTaskModelResult(TaskVersion taskVersion) {
        String sql = "insert into dzzh_task_center_version (task_id,version_code,disaster_type,create_user,create_user_name,create_time,data_from,evaluate_user," +
                "evaluate_user_name,evaluate_time,result_status,del_flag,dtcv_id) values " +
                "(:taskId,:versionCode,:disasterType,:createUser,:createUserName,:createTime,:dataFrom,:evaluateUser," +
                ":evaluateUserName,:evaluateTime,:resultStatus,:delFlag,:dtcvId)";
        BeanPropertySqlParameterSource source = new BeanPropertySqlParameterSource(taskVersion);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public TaskVersion getTaskModelFinalResult(String taskId,String finaResult) {
        String sql = "select * from dzzh_task_center_version where task_id='"+taskId+"' " +
                " and del_flag='0' and result_status='"+finaResult+"'";
        List<TaskVersion> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(TaskVersion.class));
        return PlatformObjectUtils.isNotEmpty(query)? query.get(0):new TaskVersion();
    }

    @Override
    public TaskVersion getTaskModelById(String dtcvId) {
        String sql ="select * from dzzh_task_center_version where dtcv_id='"+ dtcvId+ "'  and del_flag='0'";
        List<TaskVersion> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(TaskVersion.class));
        return PlatformObjectUtils.isNotEmpty(query)? query.get(0):new TaskVersion();
    }

    @Override
    public List<SoftSoilResult> findDataById(String dtcvId) {
        String sql = "select * from dzzh_soft_soil_evaluate_result where dtcv_id='"+dtcvId+"'";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(SoftSoilResult.class));
    }

    @Override
    public void updateTaskModelResult(TaskVersion taskVersion) {
        String sql = "update dzzh_task_center_version set update_user='"+taskVersion.getUpdateUser()+"',update_time=now(),result_status='"+taskVersion.getResultStatus()+"' where dtcv_id='"+taskVersion.getDtcvId()+"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public void batchSave(List<PolygonEvaluateResultVO> list) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(list.toArray());
        String sql =
                "insert into dzzh_soft_soil_polygon_evaluate_result(dssper_id,"
                        + "dtcv_id,"
                        + "value,"
                        + "geom,"
                        + "type,flag) "
                        + " values("
                        + ":dssperId, "
                        + ":dtcvId, "
                        + ":value, "
                        + "ST_GeometryFromText(:geom,4490), "
                        + ":type,:flag)";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    @Override
    public void deletePolygonEvaluateResult(String dtcvId) {
        jdbcTemplate.update("delete from dzzh_soft_soil_polygon_evaluate_result where dtcv_id='"+dtcvId+"'");
    }

    @Override
    public String getTaskName(String dtcvId) {
        String sql = "select task_name from dzzh_task where dt_id=(select task_id from dzzh_task_center_version where del_flag='0' and dtcv_id='"+dtcvId+"') and del_flag='0'";
        return jdbcTemplate.queryForObject(sql, String.class);
    }

    @Override
    public void deleteByDtcvId(String dtcvId) {
        String sql = "delete from dzzh_soft_soil_polygon_evaluate_result where dtcv_id='"+dtcvId+"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public List<SoftSoilDrill> getAllDrillForDp() {
        String sql = "select * from dzzh_soft_soil_drill";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(SoftSoilDrill.class));
    }
}
